<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%@ page import = "java.sql.PreparedStatement" %>
<% 
		
		String strUser = String.valueOf(session.getAttribute("sUser"));
		HttpSession htp_session = request.getSession();
				
		if (htp_session == null || htp_session.getAttribute("sUser") == null) {
		    // Forward the control to login.jsp if authentication fails or session expires
		    request.getRequestDispatcher("/login.jsp").forward(request,
		        response);
		}
		
		if(!"admin".equals(htp_session.getAttribute("sUser"))){%>
		<script>alert("This user dont have PERMISSION to access this zone.");</script>
		<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=system_select.jsp"> 	
		<%}
				
		
			
		
		//set Database Connection
		String hostProps = "";
		String usernameProps  = "";
		String passwordProps  = "";
		String databaseProps = "";
		
		try {
			//get current path
			ServletContext servletContext = request.getSession().getServletContext();
			
			InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
			Properties props = new Properties();
			
			props.load(input);
		
			hostProps  = props.getProperty("host");
			usernameProps  = props.getProperty("username");
			passwordProps  = props.getProperty("password");
			databaseProps = props.getProperty("database");
		} catch (Exception e) { 
			out.println(e);  
		}
		
		// connect database
		Connection connect = null;		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		
			connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
					"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
		
			if(connect != null){
				System.out.println("Database Connect Sucesses.");
			} else {
				System.out.println("Database Connect Failed.");	
			}
		
		} catch (Exception e) {
			out.println(e.getMessage());
			e.printStackTrace();
		}
		
		

		System.out.println("admin_employee_add_normal_background Active");
		
		
		java.util.Date dt = new java.util.Date();

		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

		String date_now = sdf.format(dt);
		

		String personal_id =  request.getParameter("personal_id");
		String title_th =  request.getParameter("title_th");
		String name_th =  request.getParameter("name_th");	
		String surname_th =  request.getParameter("surname_th");
		String name_en =  request.getParameter("name_en");	
		String surname_en =  request.getParameter("surname_en");
		String gender =  request.getParameter("gender");
		String marital_status = request.getParameter("marital_status"); //null
		String address =  request.getParameter("address"); //null
		String tel =  request.getParameter("tel"); //null'
		String email = request.getParameter("email");//null
		String facebook = request.getParameter("facebook");//null
		String picture = request.getParameter("picture");//null
		String department_id_sel = request.getParameter("department_id");
		
		if(department_id_sel.equals("no"))
		{
			department_id_sel = null;
		}
		
		String faculty_id = "1";
		String date_of_birth_in = request.getParameter("date_of_birth");
		String status = request.getParameter("status");
		
		String position = request.getParameter("position");
		
		
		String full_name_id = "";
		Date birth_date_dt  = sdf.parse(date_of_birth_in);

		
		String birth_date = sdf.format(birth_date_dt);
		
		System.out.println("date_now:"+date_now.toString());

		System.out.println("birth_date:"+birth_date);
		String dirty = "N";
		
		
		String key_index ="";
		String title_en = null;
		
	    switch(title_th){
		
			case "นาย" : title_en ="Mr."; break;
			case "นางสาว" : title_en ="Miss"; break;
			case "นาง" : title_en="Mrs."; break;
		}
		
	    System.out.println("Phase1 Parameter");
	    System.out.println("name_th:"+name_th);
		System.out.println("surname_th:"+surname_th);
		String key_index_temp="";
		
		///////////////////Phase 1 // Check Existing data in People table 
		ResultSet rs_people =	connect.createStatement().executeQuery(" SELECT P.index "
														+ " FROM people P, full_name N "
														+ " WHERE P.index=N.index "
														+ " AND N.name_th='"+name_th+"'"
														+ " AND N.surname_th='"+surname_th+"'");
	    
			


		rs_people.last();
		if(rs_people.getRow() == 1) {	
			System.out.println("This people already exist in People Table");
			key_index = rs_people.getString("index");
			System.out.println("Phase 2");
			System.out.println("Found in people table with index:"+key_index);
			
			try{
				///////////Phase 2 Add Employee
				System.out.println("key_index:"+key_index);
				System.out.println("faculty_id:"+faculty_id);
				System.out.println("department_id:"+department_id_sel);
				System.out.println("status:"+status);
				System.out.println("position:"+position);
				System.out.println("sql_statement:"+" INSERT INTO `employee`(`index`,`faculty_id`,`department_id`,"+
						" `status`, `position`)"+
						" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
						" '"+status+"', '"+position+"')");

				String sql_emp = " INSERT INTO `employee`(`index`,`faculty_id`,`department_id`,"+
						" `status`, `position`)"+
						" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
						" '"+status+"', '"+position+"')";
				
				connect.createStatement().executeUpdate(sql_emp);
				
				// log file
				String log = "admin add employee";						
				Log.writeAdminFile(log);
				Log.writeAdminFile(sql_emp);			
						
				System.out.println("Phase 2 Success");
				
						
				}catch(Exception nm){
				
					System.out.println("Phase 2 error:"+nm);
					
																
				}
				///////////////////End Phase 2/////////////////////////
		
		
		
		}else{
			
			System.out.println("Not Existing in People Table");
			
			
		  	key_index = KeyGen.genTimeStamp();
			
			/////////////////////Phase A // Add full_name to full_name table
			System.out.println("Phase A");
			System.out.println("key_index:"+key_index);
			System.out.println("Data before create full_name query");
			System.out.println(personal_id);
			System.out.println(title_th);
			System.out.println(title_en);
			System.out.println(name_th);
			System.out.println(name_en);
			System.out.println(surname_th);
			System.out.println(surname_en);
			System.out.println("gender:"+gender+":");
			System.out.println(date_now.toString());
			
			try{
				String sql_full_name = " INSERT INTO `full_name`(`index`,"+
						   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
						   " `surname_en`, `date`) "+
						   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
						   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
						   " '"+date_now+"')";
				
				PreparedStatement pstmt = connect.prepareStatement(sql_full_name, Statement.RETURN_GENERATED_KEYS);  
				pstmt.executeUpdate();  
				ResultSet keys = pstmt.getGeneratedKeys();    
				keys.next();  
				int key = keys.getInt(1);
				
				 full_name_id = Integer.toString(key);
				 
				// log file
				String log = "admin add employee";						
				Log.writeAdminFile(log);
				Log.writeAdminFile(sql_full_name);
				
				System.out.println("Phase A Success with full_name_id :"+ key);
				
			
			}catch(Exception e){
				System.out.println("Error At Phase A Add Full_name:"+e);
				dirty="Y";
				
			
			} 
			////////////////////////////// End Phase A
			
			
			
			
			
			////////////// Phase B Add to people table 
			System.out.println("Data before Add People Table");	
			System.out.println("key_index:"+key_index);
			
			System.out.println("ful_name_id:"+full_name_id);
			System.out.println("gender:"+gender);
			
			System.out.println("date_of_birth"+birth_date.toString());
			System.out.println("phonenumber"+tel);
			System.out.println("address"+address);
			System.out.println("email"+email);
			System.out.println("facebook"+facebook);
			
			
		try{
			String sql_cmd = " INSERT INTO "+"`people`(`index`, `full_name_id`,"+
					   " `gender`, `date_of_birth`, `phone_number`,"+
					   " `address`, `email`, `facebook`)"+
					   " VALUES ('"+key_index+"', '"+full_name_id+"', '"+gender+"', '"+
					    birth_date+"', '"+tel+"', "+
					   " '"+address+"', '"+email+"', '"+facebook+"')";
			
			System.out.println("sql_cmd:"+sql_cmd);
					   
			
			
			connect.createStatement().executeUpdate(sql_cmd);
			
			// log file
			Log.writeAdminFile(sql_cmd);
			
			System.out.println("Phase B Success");
			try{
				///////////Phase F Add Employee
				System.out.println("key_index:"+key_index);
				System.out.println("faculty_id:"+faculty_id);
				System.out.println("department_id:"+department_id_sel);
				System.out.println("status:"+status);
				System.out.println("position:"+position);
				System.out.println("sql_statement:"+" INSERT INTO `employee`(`index`,`faculty_id`,`department_id`,"+
						" `status`, `position`)"+
						" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
						" '"+status+"', '"+position+"')");
				
				String sql_emp = " INSERT INTO `employee`(`index`,`faculty_id`,`department_id`,"+
						" `status`, `position`)"+
						" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
						" '"+status+"', '"+position+"')";
				
				connect.createStatement().executeUpdate(sql_emp);
				
				// log file
				Log.writeAdminFile(sql_emp);
						
				System.out.println("Phase F Success");
			
						
				}catch(Exception nm){
				
					System.out.println("Phase F error:"+nm);
					dirty="Y";
																
				}
				///////////////////End Phase F	
		
			
		}catch(Exception y){
			System.out.println("Error At Phase B Add People:"+y);
			dirty="Y";
				
		}////////////////End Phase B
		
		

		if(("N").equals(dirty)){
			
			out.print("success");
		}else{
			out.println("fail");
		}
			
			
		
	}//end else not existing people

	connect.close();

%>